package org.dochandler.dao.derby;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.dochandler.config.DHConfig;
import org.dochandler.dao.MissionRequestDAO;
import org.dochandler.service.domain.mission.MissionRequest;
import org.dochandler.service.exception.DatabaseException;

@SuppressWarnings("serial")
public class DerbyMissionRequestDAO implements MissionRequestDAO, Serializable {
	
	private Connection conn;
	private String derbyUrl;
	private String derbyDriver;
	
	public DerbyMissionRequestDAO() {
		if (conn == null) {
			this.derbyUrl = DHConfig.getString("local_db_url");
			this.derbyDriver = DHConfig.getString("local_db_driver");
	
			try {
				Class.forName(derbyDriver).newInstance();
				conn = DriverManager.getConnection(derbyUrl);
			} catch (Exception e) {
				throw new DatabaseException("Could not connect to the database", e);
			}
		}
	}
	
	@Override
	public MissionRequest findById(Long entityId) {
		String sql = "SELECT * FROM MISSION_REQUEST WHERE ID = ?";
		
		MissionRequest missionRequest = null;
		try {
			PreparedStatement stmt = conn.prepareStatement(sql);
			
			int i = 0;
			stmt.setLong(++i, entityId);
			
			ResultSet rs = stmt.executeQuery(sql);
			
			if (rs.next()) {
				missionRequest = new MissionRequest();
				missionRequest.setEstimatedCost( rs.getDouble("ESTIMATED_COST") );
				missionRequest.setMissionStart( rs.getDate("MISSION_START"));
				missionRequest.setMissionEnd( rs.getDate("MISSION_END"));
				missionRequest.setRequesterId( rs.getLong("REQUESTER_ID") );
				missionRequest.setRequestDescrition( rs.getString("MISSION_DESC") );
			}
			
		} catch(SQLException e) {
			throw new DatabaseException("Could not execute find all mission requests", e);
		}
		return missionRequest;
	}

	@Override
	public List<MissionRequest> findAll() {
		String sql = "SELECT * FROM MISSION_REQUEST";
		List<MissionRequest> requests = new ArrayList<MissionRequest>();
		
		try {
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			
			while (rs.next()) {
				MissionRequest req = new MissionRequest();
				req.setEstimatedCost( rs.getDouble("ESTIMATED_COST") );
				req.setMissionStart( rs.getDate("MISSION_START"));
				req.setMissionEnd( rs.getDate("MISSION_END"));
				req.setRequesterId( rs.getLong("REQUESTER_ID") );
				req.setRequestDescrition( rs.getString("MISSION_DESC") );
				
				requests.add( req );
			}
			
		} catch(SQLException e) {
			throw new DatabaseException("Could not execute find all mission requests", e);
		}
		return requests;
	}

	@Override
	public boolean create(MissionRequest entity) {
		return (createMission(entity) == 1);
	}

	@Override
	public boolean delete(Long entityId) {
		return false;
	}

	@Override
	public boolean update(MissionRequest entity) {
		return false;
	}

	@Override
	public Long createMission(MissionRequest request) {
		String sql = "INSERT INTO MISSION_REQUEST (ESTIMATED_COST, MISSION_START, MISSION_END, REQUESTER_ID, MISSION_DESC) VALUES(?, ?, ?, ?, ?)";
		try {
			PreparedStatement stmt = conn.prepareStatement(sql);
			
			int i = 0;
			stmt.setDouble(++i, request.getEstimatedCost() );
			stmt.setDate(++i, new java.sql.Date( request.getMissionStart().getTime() ) );
			stmt.setDate(++i, new java.sql.Date( request.getMissionEnd().getTime() ) );
			stmt.setInt(++i, 0);
			stmt.setString(++i, request.getRequestDescrition());
			
			stmt.execute();
			
		} catch (SQLException e) {
			throw new DatabaseException("Could not create a mission", e);
		}
				
		return 1l;
	}
}